package edu.jptest.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.jptest.bean.Question;
import edu.jptest.util.GetDataBaseConnection;

public class QuestionDAO {

	// 插入问题
	public boolean insertQuestion(Question question) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		PreparedStatement pstm = null;
		try {
			String sql = "insert into question(question_origin_id,question_type_id,question_child,question_content_id,answer_id,test_point_id,user_id,question_subject_id,insert_time,do_times,right_counts,question_number) values(?,?,?,?,?,?,?,?,?,?,?,?)";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, question.getQuestionOriginId());
			pstm.setInt(2, question.getQuestionTypeId());
			pstm.setString(3, question.getQuestionChild());
			pstm.setInt(4, question.getQuestionContentId());
			pstm.setInt(5, question.getAnswerId());
			pstm.setInt(6, question.getTestPointId());
			pstm.setInt(7, question.getUserId());
			pstm.setInt(8, question.getQuestionSubjectId());
			pstm.setDate(9, new java.sql.Date(question.getInsertTime()
					.getTime()));
			pstm.setInt(10, question.getDoTimes());
			pstm.setInt(11, question.getRightCounts());
			pstm.setInt(12, question.getQuestionNumber());
			int result = pstm.executeUpdate();
			if (result == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
		}
		return false;
	}

	// 删除问题
	public boolean deleteQuestion(int questionId) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		PreparedStatement pstm = null;
		try {
			String sql = "delete from question where question_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, questionId);
			int result = pstm.executeUpdate();
			if (result == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
		}
		return false;
	}

	// 更该问题
	public boolean updateQuestion(Question question) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		PreparedStatement pstm = null;
		try {// ,,,,,,,,,,,,
			String sql = "update question set question_origin_id=?,question_type_id=?,question_child=?,question_content_id=?,answer_id=?,test_point_id=?,user_id=?,question_subject_id=?,insert_time=?,do_times=?,right_counts=?,question_number=? where question_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, question.getQuestionOriginId());
			pstm.setInt(2, question.getQuestionTypeId());
			pstm.setString(3, question.getQuestionChild());
			pstm.setInt(4, question.getQuestionContentId());
			pstm.setInt(5, question.getAnswerId());
			pstm.setInt(6, question.getTestPointId());
			pstm.setInt(7, question.getUserId());
			pstm.setInt(8, question.getQuestionSubjectId());
			pstm.setDate(9, new java.sql.Date(question.getInsertTime()
					.getTime()));
			pstm.setInt(10, question.getDoTimes());
			pstm.setInt(11, question.getRightCounts());
			pstm.setInt(12, question.getQuestionNumber());
			pstm.setInt(13, question.getQuestionId());
			int result = pstm.executeUpdate();
			if (result == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
		}
		return false;
	}

	// 通过问题id，查询问题对象
	public Question getQuestionByQuestionId(int question_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		Question question = new Question();
		PreparedStatement pstm = null;
		try {
			String sql = "select * from question where question_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, question_id);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				question.setQuestionId(resultSet.getInt(1));
				question.setQuestionOriginId(resultSet.getInt(2));
				question.setQuestionTypeId(resultSet.getInt(3));
				question.setQuestionChild(resultSet.getString(4));
				question.setQuestionContentId(resultSet.getInt(5));
				question.setAnswerId(resultSet.getInt(6));
				question.setTestPointId(resultSet.getInt(7));
				question.setUserId(resultSet.getInt(8));
				question.setQuestionSubjectId(resultSet.getInt(9));
				question.setExamineQuestionSubjectId(resultSet.getInt(10));
				question.setInsertTime(resultSet.getDate(11));
				question.setDoTimes(resultSet.getInt(12));
				question.setRightCounts(resultSet.getInt(13));
				question.setQuestionNumber(resultSet.getInt(14));
				return question;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return new Question();
	}

	// 通过来源id，查找该来源下的所有试题id
	public List<Integer> getQuestionsIdByOriginId(int origin_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> questionsId = new ArrayList<Integer>();
		try {
			String sql = "select question_id from question where question_origin_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, origin_id);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				questionsId.add(resultSet.getInt(1));
			}
			return questionsId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 通过试题来源、试题题目序号，获得相关条件下的试题集合
	public List<Integer> getQuestionsIdByOriginIdAndSubject(int origin_id,
			int numInBook) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> questionsId = new ArrayList<Integer>();
		try {
			String sql = "select question_id from question where question_origin_id=? and question_subject_id=(select question_subject_id from question_subject where question_book_num=?) order by question_number";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, origin_id);
			pstm.setInt(2, numInBook);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				questionsId.add(resultSet.getInt(1));
			}
			return questionsId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}


	// 通过试题来源、已组试题题目序号，获得相关条件下的试题集合
	public List<Integer> getQuestionsIdByOriginIdAndSubjectInPaper(
			int origin_id, int numInExainePaper) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> questionsId = new ArrayList<Integer>();
		try {
			String sql = "select question_id from question where question_origin_id=? and e_q_subject_id=(select e_q_subject_id from e_q_subject where num=?)";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, origin_id);
			pstm.setInt(2, numInExainePaper);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				questionsId.add(resultSet.getInt(1));
			}
			return questionsId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}


	// 通过题型id，查找相关试题集合
	public List<Integer> getQuestionsIdByType(int questiontype_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> questionsId = new ArrayList<Integer>();
		try {
			String sql = "select question_id from question where question_type_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, questiontype_id);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				questionsId.add(resultSet.getInt(1));
			}
			return questionsId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 通过题型id、考点id，查找相关试题集合
	public List<Integer> getQuestionsIdByTypeAndTestPoint(int questiontype_id,
			int testpoint_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> questionsId = new ArrayList<Integer>();
		try {
			String sql = "select question_id from question where question_type_id=? and test_point_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, questiontype_id);
			pstm.setInt(2, testpoint_id);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				questionsId.add(resultSet.getInt(1));
			}
			return questionsId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 根据作者得到试题
	public List<Integer> getQuestionsByAuthor(int authorId) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> questionsId = new ArrayList<Integer>();
		try {
			String sql = "select question_id from question where user_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, authorId);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				questionsId.add(resultSet.getInt(1));
			}
			return questionsId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 通过录入时间，查找某段时间内系统录入的试题
	public List<Integer> getQuestionsIdByTime(java.sql.Date startDate,
			java.sql.Date endDate) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> questionsId = new ArrayList<Integer>();
		try {
			String sql = "select question_id from question where insert_time>=? and insert_time<=?";
			pstm = connection.prepareStatement(sql);
			pstm.setDate(1, startDate);
			pstm.setDate(2, endDate);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				questionsId.add(resultSet.getInt(1));
			}
			return questionsId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 通过题目id， 查找此题目下的试题id
	public List<Integer> getQuestionsIdBySubjectId(int subject_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> questionsId = new ArrayList<Integer>();
		try {
			String sql = "select question_id from question where question_subject_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, subject_id);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				questionsId.add(resultSet.getInt(1));
			}
			return questionsId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 通过已组试卷中题目id， 查找试卷中此题目下的试题id
	public List<Integer> getQuestionsIdByExaminPaperSubjectId(
			int examinePaperSubject_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> questionsId = new ArrayList<Integer>();
		try {
			String sql = "select question_id from question where e_q_subject_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, examinePaperSubject_id);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				questionsId.add(resultSet.getInt(1));
			}
			return questionsId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 获得所有问题id
	public List<Integer> getAllQuestionsId() {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> questionsId = new ArrayList<Integer>();
		try {
			String sql = "select question_id from question";
			pstm = connection.prepareStatement(sql);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				questionsId.add(resultSet.getInt(1));
			}
			return questionsId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}
	
	// 通过题目id、试卷id 查找此条件下的试题集合
	public List<Integer> getQuestionsIdBySubjectIdAndExamPaperId(
			int subject_id, int examinePaper_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> questionsId = new ArrayList<Integer>();
		try {
			String sql = "select question_id from question where question_id in (select question_id from examine_question where e_p_id = ?) and question_subject_id=? ";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, examinePaper_id);
			pstm.setInt(2, subject_id);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				questionsId.add(resultSet.getInt(1));
			}
			return questionsId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	
	// 通过已组试卷中题目id、试卷id 查找该条件下的试题集合
	public List<Integer> getQuestionsIdByExaminPaperSubjectIdAndExamPaper(
			int examinePaperSubject_id,int exmainePaper_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> questionsId = new ArrayList<Integer>();
		try {
			String sql = "select question_id from question where question_id in (select question_id from examine_question where e_p_id = ?) and e_q_subject_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, exmainePaper_id);
			pstm.setInt(2, examinePaperSubject_id);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				questionsId.add(resultSet.getInt(1));
			}
			return questionsId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}
	
	// 通过题型id及试题来源id，查找相关试题集合（组卷）
	public List<Integer> getQuestionsIdByOrginAndType(int questionOrigin_id,
			int questiontype_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> questionsId = new ArrayList<Integer>();
		try {
			String sql = "select question_id from question where question_origin_id=? and question_type_id=? ";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, questionOrigin_id);
			pstm.setInt(2, questiontype_id);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				questionsId.add(resultSet.getInt(1));
			}
			return questionsId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

// 通过试题来源id、题型id、考点id，查找相关试题集合
	public List<Integer> getQuestionsIdByOriginAndTypeAndTestPoint(
			int questionOrigin_id, int questiontype_id, int testpoint_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> questionsId = new ArrayList<Integer>();
		try {
			String sql = "select question_id from question where question_origin_id =? and question_id in (select question_id from question where question_type_id=? and test_point_id=?)";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, questionOrigin_id);
			pstm.setInt(2, questiontype_id);
			pstm.setInt(3, testpoint_id);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				questionsId.add(resultSet.getInt(1));
			}
			return questionsId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

}
